﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Framework;

namespace Enterprise
{
    public class CandidatesService : BaseService
    {
        public int Add(string post, string workExperience, string education, string age,
                                      DateTime date, string language, string email, string name,
                                     string mobile, string qq, string phone, string summary, string identNumber,
                                      string workHistory, string sex, bool isRead)
        {
            Check.Argument.IsNotOutOfLength(post, 25, true, "应聘岗位");
            Check.Argument.IsNotOutOfLength(name, 25, true, "姓名");
            var sbSql = new StringBuilder();
            sbSql.Append("insert into TbCandidates(");
            sbSql.Append("CoPost,CoWorkExperience,CoEducation,CoAge,CoDate,CoLanguage,CoEmail,CoName,CoMobile,CoQq,CoPhone,CoSummary,CoIdentNumber,CoWorkHistory,CoSex,CoIsRead");
            sbSql.Append(")");
            sbSql.Append(" values (");
            sbSql.Append("@CoPost,@CoWorkExperience,@CoEducation,@CoAge,@CoDate,@CoLanguage,@CoEmail,@CoName,@CoMobile,@CoQq,@CoPhone,@CoSummary,@CoIdentNumber,@CoWorkHistory,@CoSex,@CoIsRead");
            sbSql.Append(")");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoPost", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoWorkExperience", SqlDbType.Text),
                    new SqlParameter("@CoEducation", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAge", SqlDbType.Text),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoEmail", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoMobile", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoQq", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoPhone", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSummary", SqlDbType.Text),
                    new SqlParameter("@CoIdentNumber", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoWorkHistory", SqlDbType.Text),
                    new SqlParameter("@CoSex", SqlDbType.VarChar,5),
                    new SqlParameter("@CoIsRead",SqlDbType.Bit)
                };
            parameters[0].Value = post;
            parameters[1].Value = workExperience;
            parameters[2].Value = education;
            parameters[3].Value = age;
            parameters[4].Value = date;
            parameters[5].Value = language;
            parameters[6].Value = email;
            parameters[7].Value = name;
            parameters[8].Value = mobile;
            parameters[9].Value = qq;
            parameters[10].Value = phone;
            parameters[11].Value = summary;
            parameters[12].Value = identNumber;
            parameters[13].Value = workHistory;
            parameters[14].Value = sex;
            parameters[15].Value = isRead;
            var obj = DbHelperSql.Add(sbSql.ToString(), parameters).SafeToInt();
            return obj;
        }
        public int Edit(int id, string post, string workExperience, string education, string age,
                                      DateTime date, string language, string email, string name, string mobile, string qq, string phone, string summary, string identNumber,
                                      string workHistory, string sex)
        {
            Check.Argument.IsNotOutOfLength(post, 25, true, "应聘岗位");
            Check.Argument.IsNotOutOfLength(name, 25, true, "姓名");

            var strSqlOne = new StringBuilder();
            strSqlOne.Append("update TbCandidates set ");
            strSqlOne.Append("CoPost=@CoPost");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkExperience=@CoWorkExperience");
            strSqlOne.Append(",");
            strSqlOne.Append("CoEducation=@CoEducation");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAge=@CoAge");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDate=@CoDate");
            strSqlOne.Append(",");
            strSqlOne.Append("CoLanguage=@CoLanguage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoEmail=@CoEmail");
            strSqlOne.Append(",");
            strSqlOne.Append("CoName=@CoName");
            strSqlOne.Append(",");
            strSqlOne.Append("CoMobile=@CoMobile");
            strSqlOne.Append(",");
            strSqlOne.Append("CoQq=@CoQq");
            strSqlOne.Append(",");
            strSqlOne.Append("CoPhone=@CoPhone");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSummary=@CoSummary");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIdentNumber=@CoIdentNumber");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkHistory=@CoWorkHistory");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSex=@CoSex");
            strSqlOne.Append(" where CoCandidatesId=@id;");
            SqlParameter[] parametersOne =
                {
                    new SqlParameter("@CoPost", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoWorkExperience", SqlDbType.Text),
                    new SqlParameter("@CoEducation", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAge", SqlDbType.Text),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoEmail", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoMobile", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoQq", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoPhone", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSummary", SqlDbType.Text),
                    new SqlParameter("@CoIdentNumber", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoWorkHistory", SqlDbType.Text),
                    new SqlParameter("@CoSex", SqlDbType.VarChar, 5),
                    new SqlParameter("@id", SqlDbType.Int)
                };
            parametersOne[0].Value = post;
            parametersOne[1].Value = workExperience;
            parametersOne[2].Value = education;
            parametersOne[3].Value = age;
            parametersOne[4].Value = date;
            parametersOne[5].Value = language;
            parametersOne[6].Value = email;
            parametersOne[7].Value = name;
            parametersOne[8].Value = mobile;
            parametersOne[9].Value = qq;
            parametersOne[10].Value = phone;
            parametersOne[11].Value = summary;
            parametersOne[12].Value = identNumber;
            parametersOne[13].Value = workHistory;
            parametersOne[14].Value = sex;
            parametersOne[15].Value = id;
            var obj = DbHelperSql.ExecuteSql(strSqlOne.ToString(), parametersOne).SafeToInt();
            return obj;

        }
        public int SetIsRead(int id, bool isRead)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("update TbCandidates set ");
            sbSql.Append("CoIsRead=@CoIsRead");
            sbSql.Append(" where CoCandidatesId=@id;");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoIsRead", SqlDbType.Bit),
                    new SqlParameter("@id", SqlDbType.Int)

                };
            parameters[0].Value = isRead;
            parameters[1].Value = id;
            var obj = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (obj < 1)
            {
                throw new Exception("记录不存在");
            }
            return obj;
        }
        public void Delete(int id)
        {
            DbHelperSql.ExecuteSql("delete  from TbCandidates  where CoCandidatesId={0}".FormatWith(id));
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string post, string mobile, string phone, string isRead, int pageValue, int rowsValue)
        {
            var parameters = new[]
                             {
                                 new SqlParameter("@CoPost", SqlDbType.VarChar, 255),
                                 new SqlParameter("@CoPhone", SqlDbType.VarChar, 255),
                                 new SqlParameter("@CoMobile", SqlDbType.VarChar, 255),
                                 new SqlParameter("@CoIsRead",SqlDbType.Bit)
                             };
            parameters[0].Value = post.SqlLike();
            parameters[1].Value = mobile.SqlLike();
            parameters[2].Value = phone.SqlLike();
            parameters[3].Value = isRead.SafeToBool();
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where  CoPost like '%'+@CoPost+'%'  ");
            sbSqlWhere.Append(" and  CoPhone like '%'+@CoPhone+'%'  ");
            sbSqlWhere.Append(" and  CoMobile like '%'+@CoMobile+'%'  ");
            if (!isRead.IsNullOrEmpty())
            {
                sbSqlWhere.Append(" and  CoIsRead = @CoIsRead  ");
            }
            int total = DbHelperSql.GetSingle("select count(*) from TbCandidates ".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} * from TbCandidates {1}", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and CoCandidatesId not in (select top {0} CoCandidatesId from TbCandidates {1} order by CoCandidatesId) ", rowsValue * (pageValue - 1), sbSqlWhere);
            sbSql.Append(" order by CoCandidatesId");
            var ds = DbHelperSql.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSql.Query("select *  from TbCandidates  where CoCandidatesId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
    }
}
